Problem
When running lengthy ad hoc queries or reports, or when using third-party tools for reporting from the iMIS database, locks are placed on iMIS tables. This prevents iMIS users from performing updates or inserts until the reports finish.
Solution
The problem is with lock escalation and the transaction isolation level in SQL Server. If a query affects a large percentage of rows in a table, the shared page locks are escalated to a table lock to reduce locking overhead.
There are two ways to deal with this problem:
1. Best approach - separate your transaction processing from reporting functions, so that your long-running SQL queries are run against a static database.
□ Create periodic copies of the iMIS database to use for reporting.
□ Run all reports against the copied database, while transactions go against the original database.
This is the best approach because your long report queries are run against a static database. Using NOLOCK may cause erroneous values in reports if dirty reads or phantom rows have affected the result set of the report query.
2. Alternative approach - Add the NOLOCK keyword to long-running SQL queries to keep locks from lasting as long. This forces SQL Server to change the transaction isolation level to READ UNCOMMITTED. This does not issue shared locks at either the page or table level and does not honor exclusive locks, all of which can result in erroneous query results because of possible dirty reads and phantom rows.
□ To add the NOLOCK keyword to ad hoc SQL queries (or custom scripts), add the string (nolock) to the end of the FROM clause. For example:
Select ID, LAST, ADDRESS from Name (nolock)
□ To configure iMIS to use the NOLOCK keyword automatically on specific long-running reports, perform the following task.
To create a NOLOCK keyword for large report runs
1. Obtain the iMIS SQL Server login password for the SQL Server instance on which the iMIS database exists.
Note: Some reports that have custom reporting engines (RunProcedure specified in report spec) will ignore the NOLOCK keyword. This typically affects reports that join two or more tables.
2. Using iMIS Desktop, log on to iMIS as MANAGER.
3. From the iMIS Desktop menu bar, choose Utilities > Report Specs. The Report Specifications window appears.
4. From the list of reports, select a report that is taking a long time to run and locking records for too long.
5. Click Parameters. The Detail Report Parameters window appears.
6. In the Parameters field, enter NOLOCK.
7. Click Save.
8. Repeat this process for all affected reports.
9. From the iMIS Desktop menu bar, choose File > System Setup. The SQL Security Setup window appears.
10. In the NOLOCK keyword field, enter NOLOCK.
11. Click Save.
12. Click Create SQL Logins to add the NOLOCK keyword to logins on that instance.
13. Enter the password and accept any messages about shutting down iBO on the host.